Python operation SQLite database method details [import create cursor add delete check etc.]

This article gives an example of how Python operates on the SQLite database. To share for your reference, specific as follows:

SQLite profile

SQLite, a lightweight database, is a relational database management system that complies with ACID and is included in a relatively small C library. It is a public domain project established by ES11en.RichardHipp. It is designed to be embedded, and is already used in many embedded products. Its footprint is so low that in an embedded device, only a few hundred K may be enough. It can support Windows/Linux/Unix and other mainstream operating systems. At the same time, it can combine with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interface. It is also faster than MySQL and PostgreSQL, two world-renowned open source database management systems. The first Alpha version was released in May 2000. It has been 15 years since 2015, and SQLite has also witnessed the release of a version of SQLite 3.

Use of the SQLite database

1. Import Python SQLite database module

python 2.5 builds the SQLite database

import sqlite3

2. Create/open database

When you call the connect function, specify the library name, open the specified database if it exists, or create a new one if it doesn't

conn = sqlite3.connect("E:/test.db")

Or it can be created in memory

conn = sqlite3.connect(":memory:")

3. Database connection objects

The object conn returned when the database is opened is a database connection object, which can have the following operations:

commit() -- Transaction commit
rollback() -- The transaction is rolled back
close() -- Close 1 database connection
cursor() -- Create 1 cursor

On commit(), if isolation_level isolation level defaults, you will need to use this command for every operation on the database, or you can set isolation_level=None, which will make it into auto commit mode.

4. Use a cursor to query the database

We need to query the database using the cursor object SQL statement to get the query object. Define a cursor by the following method.

cu=conn.cursor()

A cursor object has the following operations:

execute() -- Execute the sql statement
executemany -- Execute multiple sql statements
close() -- Close the cursor
fetchone() -- Takes a record from the result and points the cursor to the next record
fetchmany() -- Multiple records are taken from the results
fetchall() -- Extract all records from the results
rollback()0 -- Cursor scrolling

SQLite database operation

1. Create database tables

cu.execute("create table user (id integer primary key,name varchar(20) UNIQUE,age integer,comment text NULL)")
#  create 1 zhang user Table. There are tables id( A primary key ), The name ( only 1), age , note ( The default is empty )

2. Insert data

Please avoid the following:

#  This will result in an injection attack 
pid=200
c.execute("... where id= '%s'" % id)

The correct approach is to take the form t=(n,) if t is just a single value, because tuples are immutable.

for user in[(0,'aaa',111,'aaaa'),(1,'bbb',222,'bbbb')]:
 conn.execute("insert into user values (?,?,?,?)", user) #  Pay attention to user It's tuple, immutable 
conn.commit() #  Note that the insert is followed by a commit 

3. Query the data

cu.execute("select * from user")
cu.fetchone() #  Gets the value of the cursor 1 A value 
cu.execute("select * from user")
cu.fetchall() #  Using a cursor fetch function ,fetchall Get all query records 

4. Modify data

cu.execute("update user set name='ccc' where id = 0")
conn.commit()

Delete data

cu.execute("delete from user where id = 1")
conn.commit()

More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python data structure and algorithm tutorial", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article has been helpful for Python programming.